﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.AlbLab.GEP.Dao.Interface;
using Com.AlbLab.GEP.Dao.Base;
using Oracle.DataAccess.Client;
using Com.AlbLab.GEP.Domain.Entities;
using System.Data;
using Com.AlbLab.GEP.Domain;

namespace Com.AlbLab.GEP.Dao.Impl
{
    public class RispostaDao : OracleDao<OracleSessionManager, ISessionModel>, IRispostaDao
    {
        public RispostaDao(OracleSessionManager daoContext)
            : base(daoContext)
        {
            if (!base.Context.Session_isActive())
            {
                base.Context.Session_Init();
                base.CurrentConnection.Open();
            }
        }

        private string selectById = "select * from GEP_RISPOSTE WHERE RESP_ID_REQ=: RESP_ID_REQ";
        private string selectBylistaPratiche = "select gep_testata.tes_cod_pratica, gep_testata.tes_cod_servizio, tes_cod_contr_disp, gep_risposte.resp_xml, gep_risposte.validated from gep_testata left outer join gep_risposte on gep_testata.tes_cod_pratica=gep_risposte.resp_id_req";
        private string delete = "delete from GEP_RISPOSTE WHERE RESP_ID_REQ=: RESP_ID_REQ";
    
       
        #region IDao<GEP_BLOB,decimal> Members

        public Domain.Entities.GEP_RISPOSTA GetById(decimal id)
        {
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(selectById, CurrentConnection);
            cmd.Parameters.Add("BLB_ID_FASE", id);
            GEP_RISPOSTA ut = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        r.Read();
                        ut = new GEP_RISPOSTA();
                        ut.Resp_Id = r.GetOracleDecimal(0).Value;
                        if (!r.GetOracleClob(1).IsNull)
                        {
                            ut.Response = new System.Xml.XmlDocument();
                            ut.Response.LoadXml(r.GetOracleClob(1).Value);
                        }
                        ut.Validated = Convert.ToInt32(r.GetOracleDecimal(2).Value);
                    }
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-FD002", e0x);
            }
            return ut;
        }

        public void Insert(Domain.Entities.GEP_RISPOSTA entity)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = "INSERT INTO GEP_RISPOSTE (RESP_ID_REQ,RESP_XML,VALIDATED) VALUES (" + entity.Resp_Id + ",'" + entity.Response.OuterXml.Replace("'","''") + "',0)";
                    
                    //ocmd.Parameters.AddRange(MappingParams(entity));
                    ocmd.Connection = base.CurrentConnection;
                    ocmd.ExecuteNonQuery();
                }
                catch (Exception e0x)
                {
                    throw new ManagedException("EX-FD005", e0x);
                }
            }
        }

        public void Delete(decimal id)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = delete;
                    ocmd.Parameters.Add("RESP_ID_REQ", id);
                    ocmd.Connection = base.CurrentConnection;
                    ocmd.ExecuteNonQuery();
                }
                catch (Exception e0x)
                {
                    throw new ManagedException("EX-FS005", e0x);
                }
            }
        }

        public List<GEP_RISPOSTA> getRisposteByListaPratiche(List<decimal> codicePratiche)
        {
            int count= codicePratiche.Count;
            string s = codicePratiche[0].ToString();
            for (int i=1;i<count;i++)
                s = s + "," +codicePratiche[i].ToString();

            string sl = selectBylistaPratiche + "where  gep_testata.tes_cod_pratica in ("+ s +")";
            
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(sl, CurrentConnection);
            List<GEP_RISPOSTA> list = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        list = new List<GEP_RISPOSTA>();
                        while (r.Read())
                        {
                            GEP_RISPOSTA t= new GEP_RISPOSTA();
                            t.Resp_Id = r.GetOracleDecimal(0).Value;
                            t.Tipo_servizio= r.GetOracleString(1).Value;
                            t.Ente_servizio=r.GetOracleString(2).Value;
                            if (!r.GetOracleClob(3).IsNull)
                            {
                                t.Response = new System.Xml.XmlDocument();
                                t.Response.LoadXml(r.GetOracleClob(3).Value);
                            }
                            t.Validated = Convert.ToInt32(r.GetOracleDecimal(4).Value);
                            list.Add(t);
                        }
                    }
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-FS003", e0x);
            }
            return list; 
        }

        public Dictionary<decimal, Domain.Entities.GEP_RISPOSTA> GetAll()
        {
            throw new NotImplementedException();
        }

        public void Update(Domain.Entities.GEP_RISPOSTA entity)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = "UPDATE GEP_RISPOSTE set VALIDATED=1, RESP_XML = '" + entity.Response.OuterXml.Replace("'","''") +"' WHERE RESP_ID_REQ="+entity.Resp_Id;
                    //ocmd.Parameters.AddRange(MappingParams(entity));
                    ocmd.Connection = base.CurrentConnection;
                    ocmd.ExecuteNonQuery();
                }
                catch (Exception e0x)
                {
                    throw new ManagedException("EX-FD005", e0x);
                }
            }
        }

        #endregion

        #region IDisposable Members

        public void Dispose()
        {
            throw new NotImplementedException();
        }

        #endregion
    }
}
